﻿<%@ WebHandler Language="C#" Class="RbApsShipFinishDashBoardV2" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
/**
 * Description: 热表APS排产统计看板V2
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2024.11.27
 */

public class RbApsShipFinishDashBoardV2 : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getFactoryData":
                        r = getFactoryData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenterData":
                        r = getWorkcenterData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTeamData":
                        r = getTeamData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetails":
                        r = getDetails(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportData":
                        exportData(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT DISTINCT f2.factoryname fromfactoryname,f.factoryname tofactoryname,w.workcentername,t.teamname,cci2.containername pcontainername,
       sub.workflowstepname pworkflowstepname,
       cci.containername rbcontainername,wsp.workflowstepname sworkflowstepname,cci.qty,cci.productname,cci.productdescription,
       cci.mfgordername,aps2.starttime pstarttime,aps2.endtime pendtime,
       aps.starttime,aps.endtime,
       csd.senddate,
       csd.toacceptdate,
       csrv.completedate,
       csrv.recivedate,
       csr.checkdate,
       CASE WHEN csr.checkdate IS NULL THEN '待完成'
            WHEN csr.checkdate IS NOT NULL THEN '已完成'
       END status,
       CASE WHEN csr.checkdate IS NOT NULL AND csr.checkdate > aps.endtime THEN ROUND(csr.checkdate - aps.endtime,2)
            WHEN csr.checkdate IS NOT NULL AND csr.checkdate <= aps.endtime THEN 0
            WHEN csr.checkdate IS NULL AND SYSDATE > aps.endtime THEN ROUND(SYSDATE - aps.endtime,2)
            WHEN csr.checkdate IS NULL AND SYSDATE <= aps.endtime THEN 0
       END delaydays,
       --ape.dispatchto toperson,
       csr.reportemployeename
FROM apsplaninfo aps
LEFT JOIN workflow wf ON wf.workflowid = aps.workflowid
LEFT JOIN workflowstep wsp ON wsp.workflowid = wf.workflowid AND wsp.sequence = 1
INNER JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --子卡
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
INNER JOIN workcenter w ON w.workcenterid = wsp.workcenterid
INNER JOIN team t ON t.teamid = wsp.teamid
LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
INNER JOIN apsplandetail aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) != 0
INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = wsp.workflowstepid AND csr.usecompleted = 1 AND csr.checkdate IS NOT NULL
LEFT JOIN workflowstep ws ON ws.workflowstepid = wsp.workflowstepid
WHERE aps.isuse = 0 AND c.rbcontainertype IS NOT NULL AND csd.toacceptdate IS NOT NULL AND cci.containerstatus <> 2 AND ws.sequence = 1";

        //承制分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //主卡批次号
        if (jObject["pcontainername"] != null && jObject["pcontainername"].ToString() != "")
        {
            sql += string.Format(" AND cci2.containername = '{0}'", jObject["pcontainername"].ToString());
        }

        //热表批次号
        if (jObject["subcontainername"] != null && jObject["subcontainername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["subcontainername"].ToString());
        }

        //加工结束时间
        if (jObject["endtime"] != null && jObject["endtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["endtime"].ToString());
            sql += string.Format(" AND aps.endtime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND aps.endtime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
        }

        if (jObject["orgType"] != null && jObject["orgType"].ToString() != "")
        {
            string orgType = jObject["orgType"].ToString();
            string name = jObject["name"].ToString();
            if (orgType == "分厂")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["name"].ToString());
            }
            else if (orgType == "工区")
            {
                sql += string.Format(" AND w.workcentername = '{0}'", jObject["name"].ToString());
            }
            else
            {
                if (name == "null")
                {
                    sql += " AND t.teamname IS NULL";
                }
                else
                {
                    sql += string.Format(" AND t.teamname = '{0}'", name);
                }
            }
        }

        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() != "")
        {
            string seriesName = jObject["seriesName"].ToString();
            if (seriesName == "完成率")
            {
                sql += " AND csr.usecompleted = 1";
            }
            else if (seriesName == "未完成率")
            {
                sql += " AND NVL(csr.usecompleted,0) = 0";
            }
            else if (seriesName == "按时完成率")
            {
                sql += " AND csd.toacceptdate <= aps2.starttime AND csr.usecompleted = 1 AND csr.checkdate <= aps.endtime";
            }
            else
            {
                sql += " AND ((csr.usecompleted = 1 AND csr.checkdate > aps.endtime) OR (NVL(csr.usecompleted,0) = 0 AND SYSDATE > aps.endtime))";
            }
        }

        return sql;
    }

    //分厂数据
    Result getFactoryData(JObject jObject)
    {
        string sql = @"
SELECT COUNT(t.sworkflowstepname) total,
       SUM(DECODE(t.status,'已完成',1,0)) finishqty,
       SUM(DECODE(t.status,'已完成',0,1)) notfinishqty,
       ROUND(SUM(DECODE(t.status,'已完成',1,0)) / COUNT(t.sworkflowstepname),4) * 100 finishrate,
        ROUND(SUM(DECODE(t.status,'已完成',0,1)) / COUNT(t.sworkflowstepname),4) * 100 notfinishrate,
       SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) intimefinishQty,
       ROUND(SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) / COUNT(t.sworkflowstepname),4) * 100 intimefinishrate,
       SUM(t.delaydays) delaydays
FROM ( {0} ) t";
        sql = string.Format(sql, getQuerySql(jObject));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各工区数据
    Result getWorkcenterData(JObject jObject)
    {
        string sql = @"
SELECT t.workcentername,
       COUNT(t.sworkflowstepname) total,
       SUM(DECODE(t.status,'已完成',1,0)) finishqty,
       SUM(DECODE(t.status,'已完成',0,1)) notfinishqty,
       ROUND(SUM(DECODE(t.status,'已完成',1,0)) / COUNT(t.sworkflowstepname),4) * 100 finishrate,
        ROUND(SUM(DECODE(t.status,'已完成',0,1)) / COUNT(t.sworkflowstepname),4) * 100 notfinishrate,
       SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) intimefinishQty,
       ROUND(SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) / COUNT(t.sworkflowstepname),4) * 100 intimefinishrate,
       SUM(t.delaydays) delaydays
FROM ( {0} ) t
GROUP BY t.workcentername";
        sql = string.Format(sql, getQuerySql(jObject));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各班组数据
    Result getTeamData(JObject jObject)
    {
        string sql = @"
SELECT t.teamname,
       COUNT(t.sworkflowstepname) total,
       SUM(DECODE(t.status,'已完成',1,0)) finishqty,
       SUM(DECODE(t.status,'已完成',0,1)) notfinishqty,
       ROUND(SUM(DECODE(t.status,'已完成',1,0)) / COUNT(t.sworkflowstepname),4) * 100 finishrate,
        ROUND(SUM(DECODE(t.status,'已完成',0,1)) / COUNT(t.sworkflowstepname),4) * 100 notfinishrate,
       SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) intimefinishQty,
       ROUND(SUM(CASE WHEN t.status = '已完成' AND t.delaydays = 0 THEN 1 ELSE 0 END) / COUNT(t.sworkflowstepname),4) * 100 intimefinishrate,
       SUM(t.delaydays) delaydays
FROM ( {0} ) t
GROUP BY t.teamname";
        sql = string.Format(sql, getQuerySql(jObject));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //明细
    Result getDetails(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["fromfactoryname"].Caption = "主制分厂";
        dtResult.Columns["tofactoryname"].Caption = "承制分厂";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["pcontainername"].Caption = "主卡流水卡号";
        dtResult.Columns["pworkflowstepname"].Caption = "主卡工序";
        dtResult.Columns["rbcontainername"].Caption = "热表批次号";
        dtResult.Columns["sworkflowstepname"].Caption = "热表工序";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["pstarttime"].Caption = "计划发出时间";
        dtResult.Columns["pendtime"].Caption = "计划完成时间";
        dtResult.Columns["starttime"].Caption = "加工开始时间";
        dtResult.Columns["endtime"].Caption = "加工结束时间";
        dtResult.Columns["senddate"].Caption = "转工发出时间";
        dtResult.Columns["toacceptdate"].Caption = "转工接收时间";
        dtResult.Columns["completedate"].Caption = "转工完成时间";
        dtResult.Columns["recivedate"].Caption = "转工转入时间";
        dtResult.Columns["checkdate"].Caption = "完工时间";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["delaydays"].Caption = "超期天数";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }
}